import pandas as pd
import os
import logging

logging.basicConfig(
    filename='TrainLog.log',
    level=logging.INFO,
    format='%(asctime)s-%(levelname)s-%(message)s'
)


# 定义一个提取数据的函数
def extract_device_data(df, rows, columns):
    data = {}
    for row in rows:
        # 从DataFrame中提取特定行和列的数据
        row_data = df.iloc[row - 1, columns].tolist()
        data[f'row_{row}'] = row_data
    return data


def extract_mode_data(df, columns, row_ranges):
    data = {}
    for start, end in row_ranges:
        row_data = df.iloc[start:end, columns].values
        data[f'rows_{start}_{end}'] = [i[0] for i in row_data]
    return data


def get_single_device_data(file_path):
    df = pd.read_excel(file_path)

    columns = [3, 5, 7, 9]  # 指定要提取的列（基于0的索引）
    rows = range(3, 18)  # 指定要提取的行（基于1的索引）

    data = extract_device_data(df, rows, columns)

    devices = {}
    # 根据规则将提取的数据分配到不同的设备类型
    for i in range(1, 5):
        devices[f'device_60_{i}'] = data.get(f'row_{i + 2}', [])
    for i in range(1, 7):
        devices[f'device_50_{i}'] = data.get(f'row_{i + 6}', [])
    for i in range(1, 5):
        devices[f'device_30_{i}'] = data.get(f'row_{i + 12}', [])

    feed_frequency = []
    columns_input = [2]
    rows_input = [2]
    feed_frequency.extend(df.iloc[rows_input, columns_input].values[0])

    return {
        'feed_frequency': feed_frequency,
        'devices': devices
    }


def get_single_mode_data(file_path):
    df = pd.read_excel(file_path)

    columns = {
        'main': [3],
        'neifen': [5],
        'waifen': [7],
        'fengji': [9]
    }
    row_ranges = [(2, 6), (6, 12), (12, 16)]

    main_data = extract_mode_data(df, columns['main'], row_ranges)
    neifen_data = extract_mode_data(df, columns['neifen'], row_ranges)
    waifen_data = extract_mode_data(df, columns['waifen'], row_ranges)
    fengji_data = extract_mode_data(df, columns['fengji'], row_ranges)

    feed_frequency = []
    columns_input = [2]
    rows_input = [2]
    feed_frequency.extend(df.iloc[rows_input, columns_input].values[0])

    return (main_data['rows_2_6'], main_data['rows_6_12'], main_data['rows_12_16'],
            waifen_data['rows_2_6'], waifen_data['rows_6_12'], waifen_data['rows_12_16'],
            neifen_data['rows_2_6'], neifen_data['rows_6_12'], neifen_data['rows_12_16'],
            fengji_data['rows_2_6'], fengji_data['rows_6_12'], fengji_data['rows_12_16'],
            feed_frequency)


def get_single_col_data(file_path):
    df = pd.read_excel(file_path)

    # 读取输出数据y
    # 选择特定的列和行
    main_colume = [3]  # 第4列的列名，列减1
    neifen_colume = [5]
    waifen_colume = [7]
    fengji_colume = [9]
    rows_big_select = range(2, 16)  # 第4到第17行，行减2，左闭右开

    # 提取特定的列和行
    main_data = df.iloc[rows_big_select, main_colume].values
    neifen_data = df.iloc[rows_big_select, neifen_colume].values
    waifen_data = df.iloc[rows_big_select, waifen_colume].values
    fengji_data = df.iloc[rows_big_select, fengji_colume].values

    # 将数据转换为一维数组
    main_output_list = [i[0] for i in main_data]
    neifen_output_list = [i[0] for i in neifen_data]
    waifen_output_list = [i[0] for i in waifen_data]
    fengji_output_list = [i[0] for i in fengji_data]

    feed_frequency = []
    columns_input = [2]
    rows_input = [2]
    feed_frequency.extend(df.iloc[rows_input, columns_input].values[0])

    return main_output_list, neifen_output_list, waifen_output_list, fengji_output_list, feed_frequency


# 获取可供学习的数据
def get_single_input_data(file_path, file_name, default_input):
    df = pd.read_excel(file_path)

    input_list_data = []
    log_message = []

    # 输入行的处理
    columns_input = [9, 11, 13, 15]
    rows_input = [30]
    input_row = df.iloc[rows_input, columns_input].values[0]
    # 检查并记录NaN值，使用默认值替换
    for i, x in enumerate(input_row):
        if pd.isna(x):
            input_list_data.append(default_input[i])
            log_message.append(
                f'File:{file_name},Column {columns_input[i]}: NaN found, replaced with default value {default_input[i]}')
        else:
            input_list_data.append(x)

    columns_input = [12, 13, 14, 15, 16]
    rows_input = [26]
    input_row = df.iloc[rows_input, columns_input].values[0]
    # 检查并记录NaN值，使用默认值替换
    for i, x in enumerate(input_row):
        if pd.isna(x):
            input_list_data.append(default_input[i + 4])
            log_message.append(
                f'File:{file_name},Column {columns_input[i]}: NaN found, replaced with default value {default_input[i + 4]}')
        else:
            input_list_data.append(x)
    return input_list_data, log_message


def GetDevice_x_y(path, default):
    log_message = []
    all_files = os.listdir(path)
    # 过滤出所有Excel文件
    excel_files = [f for f in all_files if f.endswith('.xlsx') and not f.startswith('~$')]

    data = {
        'train_input': [],
        'feed_frequency': [],
        'device_60_1': [], 'device_60_2': [], 'device_60_3': [], 'device_60_4': [],
        'device_50_1': [], 'device_50_2': [], 'device_50_3': [], 'device_50_4': [], 'device_50_5': [],
        'device_50_6': [],
        'device_30_1': [], 'device_30_2': [], 'device_30_3': [], 'device_30_4': []
    }

    for file_name in excel_files:
        file_path = os.path.join(path, file_name)
        output_result = get_single_device_data(file_path)
        input_result, log_message = get_single_input_data(file_path, file_name, default)

        feed_frequency_data = output_result['feed_frequency']
        devices_data = output_result['devices']

        data['train_input'].append(input_result)
        data['feed_frequency'].append(feed_frequency_data)
        for key, value in devices_data.items():
            if key in data:
                data[key].append(value)

    return (
        data['train_input'],
        data['feed_frequency'],
        data['device_60_1'], data['device_60_2'], data['device_60_3'], data['device_60_4'],
        data['device_50_1'], data['device_50_2'], data['device_50_3'], data['device_50_4'], data['device_50_5'],
        data['device_50_6'],
        data['device_30_1'], data['device_30_2'], data['device_30_3'], data['device_30_4']
    ,log_message)


def GetMode_x_y(path, default):
    log_message = []
    # 获取所有 Excel 文件
    all_files = os.listdir(path)
    excel_files = [f for f in all_files if f.endswith('.xlsx') and not f.startswith('~$')]

    # 初始化数据容器
    data = {
        'train_input': [],
        'feed_frequency': [],
        'main_60': [], 'main_50': [], 'main_30': [],
        'neifen_60': [], 'neifen_50': [], 'neifen_30': [],
        'waifen_60': [], 'waifen_50': [], 'waifen_30': [],
        'fengji_60': [], 'fengji_50': [], 'fengji_30': []
    }

    for file_name in excel_files:
        file_path = os.path.join(path, file_name)
        (main_60_list, main_50_list, main_30_list,
         waifen_60_list, waifen_50_list, waifen_30_list,
         neifen_60_list, neifen_50_list, neifen_30_list,
         fengji_60_list, fengji_50_list, fengji_30_list,
         feed_frequency_list) = get_single_mode_data(file_path)

        train_input_list_data, log_message = get_single_input_data(file_path, file_name, default)

        # 将数据追加到列表
        data['main_60'].append(main_60_list)
        data['main_50'].append(main_50_list)
        data['main_30'].append(main_30_list)
        data['waifen_60'].append(waifen_60_list)
        data['waifen_50'].append(waifen_50_list)
        data['waifen_30'].append(waifen_30_list)
        data['neifen_60'].append(neifen_60_list)
        data['neifen_50'].append(neifen_50_list)
        data['neifen_30'].append(neifen_30_list)
        data['fengji_60'].append(fengji_60_list)
        data['fengji_50'].append(fengji_50_list)
        data['fengji_30'].append(fengji_30_list)
        data['train_input'].append(train_input_list_data)
        data['feed_frequency'].append(feed_frequency_list)

    return (data['train_input'],
            data['feed_frequency'],
            data['main_60'], data['main_50'], data['main_30'],
            data['neifen_60'], data['neifen_50'], data['neifen_30'],
            data['waifen_60'], data['waifen_50'], data['waifen_30'],
            data['fengji_60'], data['fengji_50'], data['fengji_30'], log_message)


def GetCol_x_y(path, default):
    # 获取文件夹中的所有文件
    all_files = os.listdir(path)
    # 过滤掉以~$开头的临时文件
    excel_files = [f for f in all_files if f.endswith('.xlsx') and not f.startswith('~$')]

    # 读取Excel文件
    # 读取并处理每个Excel文件
    train_input = []
    feed_frequency = []
    main = []
    neifen = []
    waifen = []
    fengji = []
    log_message = []

    for file_name in excel_files:
        file_path = os.path.join(path, file_name)
        (main_output_list, neifen_output_list, waifen_output_list, fengji_output_list,
         feed_frequency_list) = get_single_col_data(file_path=file_path)

        train_input_list, log_message = get_single_input_data(file_path, file_name, default)

        main.append(main_output_list)
        feed_frequency.append(feed_frequency_list)
        neifen.append(neifen_output_list)
        waifen.append(waifen_output_list)
        fengji.append(fengji_output_list)
        train_input.append(train_input_list)

    return train_input, feed_frequency, main, neifen, waifen, fengji, log_message
